package com.limao.DBProvider;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.limao.CacheManager.CrowdSysPriceCache;
import com.limao.CacheManager.CrowdSysProjectCache;
import com.limao.DBDataClass.CrowdHomeCommendClass;
import com.limao.DBDataClass.CrowdProjectBuyClass;
import com.limao.DBDataClass.CrowdSysDiscussDataClass;
import com.limao.DBDataClass.CrowdSysPriceDataClass;
import com.limao.DBDataClass.CrowdSysProjectClass;
import com.limao.DbConnection.DbConnectionManager;
import com.limao.Model.LzPluginException;
import com.limao.Model.PublicData;
import com.limao.Tool.CacheClass;



public class CrowdSysProjectProvider {
	
	private static class SysGoodsProviderContainer {
		private static CrowdSysProjectProvider instance = new CrowdSysProjectProvider();
	}

	public static CrowdSysProjectProvider getInstance() {
		return SysGoodsProviderContainer.instance;
	}


	// 初始化
	private CrowdSysProjectProvider() {

	}
	
	private CrowdSysProjectClass encapOrderData(ResultSet rs) throws Exception{
		
		CrowdSysProjectClass orderData = new CrowdSysProjectClass();

		orderData.projectid=rs.getString("projectid");
		orderData.userid=rs.getString("userid");
		orderData.companyid=rs.getString("companyid");
		orderData.cateid=rs.getString("cateid");
		orderData.projectname=rs.getString("projectname");
		orderData.projectimage=rs.getString("projectimage");
		orderData.licenseimage=rs.getString("licenseimage");
		

		orderData.packageprice=rs.getString("packageprice");
		
		orderData.frieght=rs.getString("frieght");
		orderData.priceunits=rs.getString("priceunits");
		
		orderData.viplevel=rs.getString("viplevel");
		orderData.days=rs.getString("days");

		
		
		orderData.supportnum=rs.getString("supportnum");
		orderData.limitenum=rs.getString("limitenum");
		
		orderData.nowrmb=rs.getString("nowrmb");
		orderData.intro=rs.getString("intro");
		orderData.introimage=rs.getString("introimage");
		orderData.extra=rs.getString("extra");
		orderData.state=rs.getString("state");
		orderData.totalmannum=rs.getString("totalmannum");
		
		orderData.ctime=rs.getString("ctime");
		orderData.utime=rs.getString("utime");
		orderData.ishome=rs.getString("ishome");
		orderData.sortv=rs.getString("sortv");
		orderData.ishomesortv=rs.getString("ishomesortv");

		
		orderData.companylogo=rs.getString("companylogo");
		orderData.companyname=rs.getString("companyname");
		orderData.address=rs.getString("address");
		orderData.tel=rs.getString("tel");

		
		orderData.cancalAbout=CacheClass.getSYS_BaseDataCacheManager().Load_Param("cancalAbout");
		orderData.cancalAboutUrl=CacheClass.getDomain()+CacheClass.getSYS_BaseDataCacheManager().Load_Param("cancalAboutUrl");
		
		//开始结束日期  
		orderData.packagepriceAbout=CacheClass.getProjectNum(orderData.packageprice.trim());
		orderData.stateAbout=CacheClass.getProjectState(orderData.state.trim());
		
		orderData.disscussnum=load_disscussnum_for_projectid(orderData.projectid);
		
		
		return orderData;
	}
	
	
	
	//&新鲜宝   众筹首页推荐的内容
	public ArrayList<CrowdHomeCommendClass> HomeCrowdSysProject()
	{
		ArrayList<CrowdHomeCommendClass> list = new ArrayList<CrowdHomeCommendClass>();
		ArrayList<CrowdSysProjectClass> list2 = new ArrayList<CrowdSysProjectClass>();
		CrowdSysProjectClass tmp = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
			try {
				 //推荐的种类名
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareCall("{call GetHomeListForGoods2()}");
				pstmt.execute();
				rs = pstmt.getResultSet();
				while (rs.next()) {
					CrowdHomeCommendClass hc=new CrowdHomeCommendClass();
					hc.cateid=rs.getString("cateid");
					hc.cateimage=rs.getString("cateimage");
					hc.catename=rs.getString("catename");
					list.add(hc);
				}
				//推荐的项目
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareCall("{call GetHomeListForGoods()}");
				pstmt.execute();
				rs = pstmt.getResultSet();
				while (rs.next()) {
					tmp = encapOrderData(rs);
					list2.add(tmp);
				}

			} catch (Exception e) {
				e.printStackTrace();
				list .clear();
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}
			
			if(list.size()>0 && list2.size()>0)
			{
				for(CrowdHomeCommendClass hc:list)//推荐的种类名
				{
					hc.childlist=new ArrayList<CrowdSysProjectClass>();
					int cateid=CacheClass.getIntValue(hc.cateid);
					for(CrowdSysProjectClass tmp2:list2) //推荐的项目
					{
						if(cateid==CacheClass.getIntValue(tmp2.cateid))
						{
							hc.childlist.add(tmp2);
						}
					}
				}
			}
			
			

			return list;
			
	}
	
	
	//&新鲜宝   取众筹项目列表信息
	public PublicData getGoods_ListForCommon(int cateid,String keyword,int curpage,int priceid)
	{
		
		Connection con = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    PublicData tmp=null;
	    ArrayList<Object> listArry= null;
	    try {
	   	
	       List<CrowdSysProjectClass> authObjList= new ArrayList<CrowdSysProjectClass>();
	       int  total=0;
	       float minpri=0;
	       float maxpri=0;
	       if(priceid!=0)
	       {
	    	   CrowdSysPriceDataClass obj=CrowdSysPriceCache.getInstance().load_PriceByPriceid(priceid);
	    	   minpri=CacheClass.getFloatValue(obj.minprice);
	    	   maxpri=CacheClass.getFloatValue(obj.maxprice);
	       }
	       con = DbConnectionManager.getConnection();
		   pstmt = con.prepareCall("{call GetGoodsList(?,?,?,?,?)}"); 
		   pstmt.setInt(1, cateid);
		   pstmt.setString(2, keyword);
	       pstmt.setInt(3, curpage);
	       pstmt.setFloat(4, minpri);
	       pstmt.setFloat(5, maxpri);

	       boolean hadResults=pstmt.execute();
	       int mm=0;
	       while (hadResults) {
	       	if(tmp==null) tmp=new PublicData();
	          	rs=pstmt.getResultSet();
	          	if(mm==0)
	          	{
	          		while(rs != null && rs.next())
	          		{
	          			CrowdSysProjectClass goodsTmp=encapOrderData(rs);
	          			authObjList.add(goodsTmp);
	          		}
	          	}
	         	else 
	          	{
	          		while(rs != null && rs.next()){
	          			total=total+rs.getInt(1);
	          		}
	          	}
	           hadResults = pstmt.getMoreResults(); //检查是否存在更多结果集
	           mm++;
	       }
	       DbConnectionManager.closeConnection(rs, pstmt, con);	      
	       
	       if(authObjList.size()>0)
	       {
	    	   if(listArry==null) listArry=new ArrayList<Object>();
	    	   listArry.addAll(authObjList);
	       }
			
			if((total%20)>0)
			{
				total=(total/20+1);
			}
			else
				total=(total/20);
			
			if(total==0) total=1;
			tmp.total=total+"";

			

	       tmp.curpage=curpage+"";
	       tmp.list=listArry;

	  }
	  catch (Exception e) {
	  		tmp=null;
	      e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}


	   return tmp;


    }		
	
	
	//&新鲜宝  取某众筹详细信息（项目主页）  不关心有效与否
	public CrowdSysProjectClass load_Goods_Info_ById(int projectid)
	{
			
		CrowdSysProjectClass tmp = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select pr.*,c.companyname,c.companyman,c.companylogo,c.tel,c.address,c.qq,c.licenseimage from cd_sys_goods pr "+
				" inner join cd_sys_company c on (c.companyid=pr.companyid) " +
				" where pr.projectid=? ");
			pstmt.setInt(1, projectid);
			rs = pstmt.executeQuery();
		
			if (rs.next()) {
				tmp=encapOrderData(rs);
			}

		} catch (Exception e) {
			tmp = null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return tmp;
	}
	
	
	//&新鲜宝  取某众筹详细信息（项目主页）  有效的
	public CrowdSysProjectClass load_Goods_Info_ById2(int projectid)
	{
			
		CrowdSysProjectClass tmp = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select pr.*,c.companyname,c.companyman,c.companylogo,c.tel,c.address,c.qq,c.licenseimage from cd_sys_goods pr "+
				" inner join cd_sys_company c on (c.companyid=pr.companyid) " +
				" where pr.projectid=? and pr.state!=0");
			pstmt.setInt(1, projectid);
			rs = pstmt.executeQuery();
		
			if (rs.next()) {
				tmp=encapOrderData(rs);
			}

		} catch (Exception e) {
			tmp = null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return tmp;
	}
	
	//&新鲜宝  取某众筹详细信息（话题页）
  	public PublicData load_Goods_Discuss_ById(int projectid,int curpage) throws LzPluginException
  	{
  		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		PublicData tmp = null;
		ArrayList<Object> listArry = null;

		CrowdSysProjectClass project = CrowdSysProjectCache.getInstance()
				.load_Goods_Info_ById(projectid);
		if (project == null) {
			throw new LzPluginException("众筹不存在");
		}
		try {

			List<CrowdSysDiscussDataClass> authObjList = new ArrayList<CrowdSysDiscussDataClass>();
			int total = 0;

			con = DbConnectionManager.getConnection();
			pstmt = con.prepareCall("{call GetProjectDiscuss(?,?)}");
			pstmt.setInt(1, projectid);
			pstmt.setInt(2, curpage);

			boolean hadResults = pstmt.execute();
			int mm = 0;
			while (hadResults) {
				if (tmp == null)
					tmp = new PublicData();
				rs = pstmt.getResultSet();
				if (mm == 0) {
					while (rs != null && rs.next()) {
						CrowdSysDiscussDataClass goodsTmp = new CrowdSysDiscussDataClass();
						goodsTmp.disscussid = rs.getString("disscussid");
						goodsTmp.userid = rs.getString("userid");
						goodsTmp.replayid = rs.getString("replayid");
						goodsTmp.txt = rs.getString("txt");
						goodsTmp.ctime = CacheClass.timeLogic(
								rs.getLong("ctime"), "MM月dd日");

						goodsTmp.username = rs.getString("username");
						goodsTmp.avatar = rs.getString("avatar");

						authObjList.add(goodsTmp);
					}
				} else {
					while (rs != null && rs.next()) {
						total = total + rs.getInt(1);
					}
				}
				hadResults = pstmt.getMoreResults(); // 检查是否存在更多结果集
				mm++;
			}

			if (authObjList.size() > 0) {
				for (CrowdSysDiscussDataClass obj : authObjList) {
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con
							.prepareStatement("select sql_calc_found_rows d.*,a.username,a.avatar from cd_sys_disscuss d "
									+ " left join cd_user_auth a on a.userid=d.userid  "
									+ " where d.projectid=? and replayid=? order by d.projectid asc");
					pstmt.setInt(1, projectid);
					pstmt.setInt(2, CacheClass.getIntValue(obj.disscussid));
					rs = pstmt.executeQuery();
					while (rs.next()) {
						if (obj.childlist == null)
							obj.childlist = new ArrayList<CrowdSysDiscussDataClass>();
						CrowdSysDiscussDataClass goodsTmp = new CrowdSysDiscussDataClass();
						goodsTmp.disscussid = rs.getString("disscussid");
						goodsTmp.userid = rs.getString("userid");
						goodsTmp.replayid = rs.getString("replayid");
						goodsTmp.txt = rs.getString("txt");
						goodsTmp.ctime = CacheClass.timeLogic(
								rs.getLong("ctime"), "MM月dd日");

						goodsTmp.username = rs.getString("username");
						goodsTmp.avatar = rs.getString("avatar");

						obj.childlist.add(goodsTmp);

					}
				}
			}

			if (authObjList.size() > 0) {
				if (listArry == null)
					listArry = new ArrayList<Object>();
				listArry.addAll(authObjList);
			}

			if ((total % 10) > 0) {
				tmp.total = (total / 10 + 1) + "";
			} else
				tmp.total = (total / 10) + "";

			tmp.curpage = curpage + "";
			tmp.list = listArry;
			tmp.userid = project.userid;

		} catch (Exception e) {
			tmp = null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}

  	   return tmp;
    }
    
    
  	
  	/** 发布话题 或回复 **/
  	public boolean insertDiscuss_ByProjectid(int userid,int projectid,int replayid,String tex)
  	{
 		boolean tmp = false;
 		Connection con = null;
 		PreparedStatement pstmt = null;
 		ResultSet rs = null;
 		try {

 			con = DbConnectionManager.getConnection();
 			pstmt = con.prepareStatement("insert into cd_sys_disscuss (userid,projectid,replayid,txt,ctime) values (?,?,?,?,?) ");
 			pstmt.setInt(1, userid);
 			pstmt.setInt(2, projectid);
 			pstmt.setInt(3, replayid);
 			pstmt.setString(4, tex);
 			pstmt.setLong(5, CacheClass.getTime());
 			pstmt.execute();
 			
 			//更新众筹话题数（只更新到缓存，缓存没有取新的时，这时重新从数据库表中统计数量）
 			CrowdSysProjectCache.getInstance().update_projectDisscussnum(projectid);
 		
 			tmp=true;

 		} catch (Exception e) {
 			tmp = false;
 			e.printStackTrace();
 		} finally {
 			DbConnectionManager.closeConnection(rs, pstmt, con);
 		}
 		return tmp;	
  	}
  	
  	
  	
  	
  	
  	
    //&新鲜宝  取话题数量
	public String load_disscussnum_for_projectid(String projectid)
			throws LzPluginException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String num = "0";
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con
					.prepareStatement("SELECT COUNT(disscussid) as num from cd_sys_disscuss where projectid=? and replayid=0;");
			pstmt.setInt(1, CacheClass.getIntValue(projectid));
			rs = pstmt.executeQuery();
			if (rs.next()) {
				num = rs.getString("num");
			}
		} catch (Exception e) {
			num = "0";
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}

		return num;
	}
 	
 	 //&新鲜宝  取某众筹详细信息（支持者）
 	public PublicData load_Goods_Buy_ById(int projectid,int curpage) throws LzPluginException
  	{
  		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		PublicData tmp = null;
		ArrayList<Object> listArry = null;

		try {

			List<CrowdProjectBuyClass> authObjList = new ArrayList<CrowdProjectBuyClass>();
			int total = 0;

			con = DbConnectionManager.getConnection();
			pstmt = con.prepareCall("{call GetProjectBuy(?,?)}");
			pstmt.setInt(1, projectid);
			pstmt.setInt(2, curpage);

			boolean hadResults = pstmt.execute();
			int mm = 0;
			while (hadResults) {
				if (tmp == null)
					tmp = new PublicData();
				rs = pstmt.getResultSet();
				if (mm == 0) {
					while (rs != null && rs.next()) {
						CrowdProjectBuyClass goodsTmp = new CrowdProjectBuyClass();

						goodsTmp.userid = rs.getString("userid");
						goodsTmp.username = rs.getString("username");
						goodsTmp.avatar = rs.getString("avatar");
						goodsTmp.totalrmb = rs.getString("totalrmb");
						goodsTmp.starttime = rs.getString("starttime");

						authObjList.add(goodsTmp);
					}
				} else {
					while (rs != null && rs.next()) {
						total = total + rs.getInt(1);
					}
				}
				hadResults = pstmt.getMoreResults(); // 检查是否存在更多结果集
				mm++;
			}

			if (authObjList.size() > 0) {
				if (listArry == null)
					listArry = new ArrayList<Object>();
				listArry.addAll(authObjList);
			}

			if ((total % 20) > 0) {
				total = (total / 20 + 1);
			} else
				total = (total / 20);

			if (total == 0)
				total = 1;
			tmp.total = total + "";

			tmp.curpage = curpage + "";
			tmp.list = listArry;

		} catch (Exception e) {
			tmp = null;
			e.printStackTrace();

		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}

  	    return tmp;
    }
     	


}
